﻿const query = require('../client');
const moment = require('moment');
const createGuid = require('../lib/util').createGuid;
const dateToString = require('../lib/util').dateToString;

module.exports = {
    // 获取字典表中全部的成绩子项列表
    getDList: (callback) => {
        let sql = `SELECT dssi.PK_ScoreSubItemGuid AS FK_ScoreSubItemGuid,dssi.FK_ScoreSubItemTypeGuid,dssi.\`Name\` AS ScoreSubItemName,
            dssi.Description AS ScoreSubItemDescription,dssi.ComputeScoreType,dssi.IconUrl AS ScoreSubItemIconUrl,
            dssi.BackgroundColor AS ScoreSubItemBackgroundColor,dssit.\`Name\` AS ScoreSubItemTypeName,
            dssit.IconUrl AS ScoreSubItemTypeIconUrl 
            FROM d_scoresubitem AS dssi JOIN d_scoresubitemtype AS dssit ON dssit.PK_ScoreSubItemTypeGuid=dssi.FK_ScoreSubItemTypeGuid 
            WHERE dssi.\`Status\`<99 AND dssit.\`Status\`<99`;
        query(sql, function(err, vals) {
            if (err) {
                console.log(err);
                callback(0, err);
                return;
            }
            callback(1, vals);
        });
    },
    // 获取实际数据中全部的自定义的成绩子项列表
    getUserDefineList: (classGuid, scoreSegmentGuid, callback) => {
        let sql = `SELECT dssi.PK_ScoreSubItemGuid AS FK_ScoreSubItemGuid,dssi.FK_ScoreSubItemTypeGuid,ssissi.ScoreSubItemName,
            dssi.Description AS ScoreSubItemDescription,dssi.ComputeScoreType,dssi.IconUrl AS ScoreSubItemIconUrl,
            dssi.BackgroundColor AS ScoreSubItemBackgroundColor,dssit.\`Name\` AS ScoreSubItemTypeName,
            dssit.IconUrl AS ScoreSubItemTypeIconUrl 
            FROM scoresegment_in_scoresubitem AS ssissi JOIN d_scoresubitem AS dssi ON dssi.PK_ScoreSubItemGuid=ssissi.FK_ScoreSubItemGuid 
            JOIN d_scoresubitemtype AS dssit ON dssit.PK_ScoreSubItemTypeGuid=dssi.FK_ScoreSubItemTypeGuid 
            WHERE ssissi.\`Status\`<99 AND dssi.\`Status\`<99 AND dssit.\`Status\`<99 AND dssi.ComputeScoreType=1 
            AND ssissi.FK_ClassGuid='${classGuid}' AND ssissi.FK_ScoreSegmentGuid='${scoreSegmentGuid}'`;
        query(sql, function(err, vals) {
            if (err) {
                console.log(err);
                callback(0, err);
                return;
            }
            callback(1, vals);
        });
    },
    // 获取某个班的成绩子项列表
    getListOfClass: (classGuid, callback) => {
        let sql = `SELECT ssissi.FK_ClassGuid,ssissi.FK_ScoreSegmentGuid,ssissi.FK_ScoreSubItemGuid AS PK_ScoreSubItemGuid,dssi.FK_ScoreSubItemGuid,dssi.\`Name\` AS ScoreSubItemName,dssi.Description AS ScoreSubItemDescription,ssissi.ScoreSubItemWeight,
            dssi.ComputeScoreType,dssi.IconUrl AS ScoreSubItemIconUrl,dssi.BackgroundColor AS ScoreSubItemBackgroundColor,dssi.FK_ScoreSubItemTypeGuid,
            dssit.\`Name\` AS ScoreSubItemTypeName,dssit.IconUrl AS ScoreSubItemTypeIconUrl 
            FROM scoresegment_in_scoresubitem AS ssissi JOIN d_scoresubitem AS dssi ON dssi.PK_ScoreSubItemGuid=ssissi.FK_ScoreSubItemGuid 
            JOIN d_scoresubitemtype AS dssit ON dssit.PK_ScoreSubItemTypeGuid=dssi.FK_ScoreSubItemTypeGuid 
            WHERE ssissi.\`Status\`<99 AND dssi.\`Status\`<99 AND dssit.\`Status\`<99 AND ssissi.FK_ClassGuid='${classGuid}'`;
        query(sql, function(err, vals) {
            if (err) {
                console.log(err);
                callback(0, err);
                return;
            }
            callback(1, vals);
        });
    },
    // 获取某个成绩环节的全部成绩子项列表
    getList: (scoreSegmentGuid, callback) => {
        let sql = `SELECT dssi.PK_ScoreSubItemGuid,dssi.FK_ScoreSubItemTypeGuid,dssi.\`Name\`,dssi.ComputeScoreType,
            dssi.IconUrl,dssi.BackgroundColor,dssit.\`Name\` AS ScoreSubItemTypeName 
            FROM d_scoresubitem AS dssi JOIN d_scoresubitemtype AS dssit ON dssit.PK_ScoreSubItemTypeGuid=dssi.FK_ScoreSubItemTypeGuid 
            WHERE dssi.\`Status\`<99 AND dssit.\`Status\`<99 `;
        query(sql, function(err, vals) {
            if (err) {
                console.log(err);
                callback(0, err);
                return;
            }
            callback(1, vals);
        });
    },
    // 添加某个班的成绩环节与成绩子项的联系
    create: (scoreSegmentScoreSubItemGuid, params, creatorGuid, callback) => {
        let sql = `INSERT INTO scoresegment_in_scoresubitem(PK_ScoreSegmentScoreSubItemGuid,FK_ClassGuid,FK_ScoreSegmentGuid,FK_ScoreSubItemGuid,ScoreSubItemName,ScoreSubItemWeight,
            Status,FK_CreatorGuid,CreateTime,CreateIP) 
            VALUES('${scoreSegmentScoreSubItemGuid}','${params.FK_ClassGuid}','${params.FK_ScoreSegmentGuid}','${params.FK_ScoreSubItemGuid}','${params.ScoreSubItemName}',
            '${params.ScoreSubItemDescription}',${params.ScoreSubItemWeight},1,'${creatorGuid}',SYSDATE(),${ip})`;
        query(sql, function(err, vals) {
            if (err) {
                console.log(err);
                callback(0, err);
                return;
            }
            callback(1, vals);
        });
    },
    // 添加某个班的成绩环节与成绩子项的联系
    createUserDefineSubItem: (scoreSegmentScoreSubItemGuid, params, creatorGuid, callback) => {
        let sql = `INSERT INTO scoresegment_in_scoresubitem(PK_ScoreSegmentScoreSubItemGuid,FK_ClassGuid,FK_ScoreSegmentGuid,FK_ScoreSubItemGuid,ScoreSubItemName,ScoreSubItemWeight,
            Status,FK_CreatorGuid,CreateTime,CreateIP) 
            VALUES('${scoreSegmentScoreSubItemGuid}','${params.FK_ClassGuid}','${params.FK_ScoreSegmentGuid}','${params.FK_ScoreSubItemGuid}','${params.ScoreSubItemName}',
            '${params.ScoreSubItemDescription}',0,1,'${creatorGuid}',SYSDATE(),${ip})`;
        query(sql, function(err, vals) {
            if (err) {
                console.log(err);
                callback(0, err);
                return;
            }
            callback(1, vals);
        });
    },
    // 添加某个班的成绩环节与成绩子项的联系（批量）
    createList: (classGuid, params, creatorGuid, ip, callback) => {
        let createGuidStr = module.exports.strToSqlStr(creatorGuid);
        // 先更新，找出不存在的联系
        let noExistArr = [],   // 不存在的联系
            subItemIndexTemp = 0;
        updateAsync(classGuid, params, subItemIndexTemp, createGuidStr, function(status, noExistArr){
            if (noExistArr.length > 0)
            {
                let sql = `INSERT INTO scoresegment_in_scoresubitem(PK_ScoreSegmentScoreSubItemGuid,FK_ClassGuid,FK_ScoreSegmentGuid,FK_ScoreSubItemGuid,ScoreSubItemName,
                    ScoreSubItemDescription,ScoreSubItemWeight,\`Status\`,FK_CreatorGuid,CreateTime,CreateIP) 
                    VALUES`;
                noExistArr.forEach(subItem => {
                    sql += `(UUID(),'${classGuid}','${subItem.FK_ScoreSegmentGuid}','${subItem.FK_ScoreSubItemGuid}','${subItem.ScoreSubItemName}',`;
                    sql += `${module.exports.strToSqlStr(subItem.ScoreSubItemDescription)},`;
                    sql += `${subItem.ScoreSubItemWeight},1,`;
                    sql += `${createGuidStr},SYSDATE(),'${ip}'),`;
                });
                sql = sql.substring(0, sql.length - 1);
                query(sql, function(err, vals) {
                    if (err) {
                        console.log(err);
                        callback(0, err);
                        return;
                    }
                    callback(1, vals);
                });
            }
            else
            {
                callback(1, {});
            }
        });

        // 内部函数：异步更新一个list；得到不存在的联系
        function updateAsync(classGuid, subItemList, subItemIndex, creatorGuid, callback)
        {
            let subItem = subItemList[subItemIndex];
            let sql = `UPDATE scoresegment_in_scoresubitem SET \`Status\`=1,FK_ModifierGuid=${createGuidStr},ModifyTime=SYSDATE(),ModifyIP='${ip}' 
                WHERE FK_ClassGuid='${classGuid}' AND FK_ScoreSegmentGuid='${subItem.FK_ScoreSegmentGuid}' AND FK_ScoreSubItemGuid='${subItem.FK_ScoreSubItemGuid}'`;
            
            query(sql, function(err, vals){
                if (err) {
                    console.log(err);
                    callback(0, err);
                    return;
                }
                else if (vals.affectedRows == 0)
                {
                    noExistArr.push(subItem);
                }
    
                if (subItemIndex < subItemList.length - 1)
                {
                    updateAsync(subItemList, subItemIndex + 1, creatorGuid, callback);
                }
                else
                {
                    callback(2, noExistArr);
                }
            });
        }
    },
    // 删除某个班的成绩环节与成绩子项的联系
    delete: (scoreSegmentGuid, scoreSubItemGuid, creatorGuid, ip, callback) => {
        let sql = `UPDATE scoresegment_in_scoresubitem SET \`Status\`=99 WHERE FK_ScoreSegmentGuid='${scoreSegmentGuid}' AND FK_ScoreSubItemGuid='${scoreSubItemGuid}'`;
        query(sql, function(err, vals) {
            if (err) {
                console.log(err);
                callback(0, err);
                return;
            }
            callback(1, vals);
        });
    },
    // 删除某个班的成绩环节与成绩子项的联系（批量）
    deleteList: (classGuid, params, deleterGuid, ip, callback) => {
        let sql = `UPDATE scoresegment_in_scoresubitem SET \`Status\`=99,`;
        let deleterGuidStr = module.exports.strToSqlStr(deleterGuid);
        sql += `FK_DeleterGuid=${deleterGuidStr},DeleteTime=SYSDATE(),DeleteIP='${ip}' WHERE `;
        params.forEach(subItem=>{
            sql += `(FK_ClassGuid='${classGuid}' AND FK_ScoreSegmentGuid='${subItem.FK_ScoreSegmentGuid}' AND FK_ScoreSubItemGuid='${subItem.PK_ScoreSubItemGuid}') OR `;
        });
        if (params.length > 0)
        {
            sql = sql.substring(0, sql.length - 4);
        }
        query(sql, function(err, vals) {
            if (err) {
                console.log(err);
                callback(0, err);
                return;
            }
            callback(1, vals);
        });
    },
    // 保存权重
    saveWeight: function(params, ip, callback){
        if (params.length > 0)
        {
            let sql = ``;
            params.forEach(subItem=>{
                sql += `UPDATE scoresegment_in_scoresubitem SET ScoreSubItemWeight=${subItem.ScoreSubItemWeight},FK_ModifierGuid=null,ModifyTime=SYSDATE(),ModifyIP='${ip}' 
                    WHERE FK_ClassGuid='${subItem.FK_ClassGuid}' AND FK_ScoreSegmentGuid='${subItem.ScoreSegmentGuid}' AND FK_ScoreSubItemGuid='${subItem.ScoreSubItemGuid}';`;
            });
            sql = sql.substring(0, sql.length - 1);
            query(sql, function(err, vals) {
                if (err) {
                    console.log(err);
                    callback(0, err);
                    return;
                }
                callback(1, vals);
            });
        }
        else
        {
            callback(1, {});
        }
    },
    // 字符串转换为sql语句内容
    strToSqlStr: function(str, hasComma = false)
    {
        if (str)
        {
            if (hasComma)
            {
                return `'${str}',`;
            }
            else
            {
                return `'${str}'`;
            }
        }
        else
        {
            if (hasComma)
            {
                return `null,`;
            }
            else
            {
                return `null`;
            }
        }
    },
    // 初始化班级，成绩环节，成绩子项
    initClassScoreSubItem: function(classGuid, ip, callback)
    {
        let sqlstr = `INSERT INTO scoresegment_in_scoresubitem(PK_ScoreSegmentScoreSubItemGuid,FK_ClassGuid,FK_ScoreSegmentGuid,FK_ScoreSubItemGuid,ScoreSubItemWeight,Status,CreateTime,CreateIP) 
            SELECT UUID(),'${classGuid}',FK_ScoreSegmentGuid,FK_ScoreSubItemGuid,TemplateWeight,1,SYSDATE(),'${ip}' FROM t_scoresegment_scoresubitem WHERE Status<99`;
        query(sqlstr, function(err, vals){
            if (err) {
                console.log(err);
                callback(0, err);
                return;
            }
            callback(1, vals);
        });
    }
};